package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;
import com.neusoft.elm.po.Admin;

public class BusinessDaoImpl implements BusinessDao {

	
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	
	
	@Override
	public List<Business> listBusiness(String businessName,String businessAddress) {
		
		
		
		//String sql = "SELECT * FROM business WHERE 1=1";
		StringBuffer sql  = new StringBuffer("SELECT * FROM business WHERE 1=1 ");
		
		//商家名称
		if(businessName!=null && !businessName.equals("")){
			sql.append("AND businessname LIKE '%" + businessName + "%' ");
		}
		
		//商家地址
		if(businessAddress!=null && !businessAddress.equals("")){
			sql.append("AND businessAddress LIKE '%" + businessAddress + "%' ");
		}
		
		
		
		List<Business> list = new ArrayList<>();
		

		try {
			//1.连接数据库
			con =  DBUtil.getConnection();
			//设置返回自增长的列的值
			
			pst = con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			
			while(rs.next()){
				Business business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				list.add(business);
				
				
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		
		
		return list;
	}


	@Override
	public int saveBusiness(String businessName) {
		int businessId = 0;
		String sql = "INSERT INTO business (businessName,password) VALUES (?,'123') ";
		try {
			//连接数据库
			con = DBUtil.getConnection();
			//设置返回自增长的列的值
			pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			pst.setString(1, businessName);
			pst.executeUpdate();
			//获取自动增长列(一行一列)
			rs = pst.getGeneratedKeys();
			if(rs.next()){
				businessId = rs.getInt(1);//参数是取回来第一列的值
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pst, con);
		}
		return businessId;
	}






	@Override
	public int removeBusiness(int businessId) {
		int result = 0;
		
		String delFoodSql = "DELETE FROM food WHERE businessId = ?";
		String delBusinessSql = "DELETE FROM business  WHERE businessId = ?";
		
		// TODO Auto-generated method stub
		try {
			// 1.连接数据库
			con = DBUtil.getConnection();
			
			//2.开启一个事务
			con.setAutoCommit(false);
			
			//设置返回自增长的列的值
			//删除食品信息
			pst = con.prepareStatement(delFoodSql);
			pst.setInt(1, businessId);
			pst.executeUpdate();
			
			//删除商家信息
			pst = con.prepareStatement(delBusinessSql);
			pst.setInt(1, businessId);
			result = pst.executeUpdate();
			
			//事物的提交
			con.commit();
			
		} catch (Exception e) {
			result = 0;
			try {
				//事物的回滚
				con.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			
		} finally {
			DBUtil.close(rs, pst, con);
		}
		
		return result;
	}






	@Override
	public Business getBusinessByIdByPass(Integer businessId, String password) {
		
		String sql="SELECT * FROM business WHERE businessId = ?  AND password = ? ";
		Business business = null;
		try {
			//1.连接数据库
			con =  DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			//设置返回自增长的列的值
			
			pst.setInt(1, businessId);
			pst.setString(2, password);
			//获取自动增长列(一行一列)
			rs = pst.executeQuery();
			
			while(rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				
				
			}
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		
		return business;
	}






	@Override
	public Business showBusiness(Integer businessId) {
		String sql="SELECT * FROM business WHERE businessId = ? ";
		
		Business business = null;
		
		try {
			//1.连接数据库
			con =  DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			//设置返回自增长的列的值
			
			pst.setInt(1, businessId);
			
			//获取自动增长列(一行一列)
			rs = pst.executeQuery();
			
			
			while(rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				
				
				
			}
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pst, con);
		}
		
		return business;
		
	}






	@Override
	public int updateBusiness(Business business) {
		int result = 0;
		String sql = "UPDATE business SET businessName = ? , businessAddress = ? , businessExplain = ? , starPrice = ? , deliveryPrice=? WHERE businessId = ?";
		try {
			//连接数据库
			con = DBUtil.getConnection();
			//设置返回自增长的列的值
			pst = con.prepareStatement(sql);
			pst.setString(1, business.getBusinessName());
			pst.setString(2, business.getBusinessAddress());
			pst.setString(3, business.getBusinessExplain());
			pst.setDouble(4, business.getStarPrice());
			pst.setDouble(5, business.getDeliveryPrice());
			pst.setInt(6, business.getBusinessId());
			
			result = pst.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pst, con);
		}
		return result;
	}


	@Override
	public int updateBusinessByPassword(Integer businessId, String password) {
		// TODO Auto-generated method stub
		
		
		int result = 0;
		String sql = "UPDATE business SET password = ? WHERE businessId = ?";
		try {
			//连接数据库
			con = DBUtil.getConnection();
			//设置返回自增长的列的值
			pst = con.prepareStatement(sql);
			pst.setString(1, password);
			pst.setInt(2,businessId);
			
			
			result = pst.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pst, con);
		}
		
		
		return result;
	}
	}


